# Direct SQL [Query indexed data directly in Postgres]

It's often useful to query Ponder tables directly in Postgres.

:::info
Direct SQL is more complex than querying over HTTP with GraphQL or SQL over HTTP. If either of those options are sufficient for your use case, consider using them instead.
:::

## Limitations

As a rule of thumb, the _only_ supported operation is `SELECT` queries against Ponder tables.

- **Direct SQL queries are read-only**. Direct SQL queries should not insert, update, or delete rows from Ponder tables.
- **Direct SQL with zero-downtime deployments requires additional setup**. [Read more](/docs/production/self-hosting) about the views pattern in the production guide.
- **Triggers on Ponder tables are not supported**.

## Database schema

Ponder uses **database schemas** to isolate deployments. [Read more](/docs/production/self-hosting#database-schema) in the self-hosting guide.

Direct SQL queries should target the database schema corresponding to a specific deployment. Or, if your project uses the [views pattern](/docs/production/self-hosting#views-pattern) (**recommended**), queries should target the views schema.

Here are a few strategies to configure the database schema.

### Manual / hard-coded

The easiest way to target a specific database schema is to specify it manually in each SQL query. This works well for projects using the views pattern, because the schema changes less frequently.

```sql [Direct SQL query]
SELECT *
FROM my_ponder_project.accounts -- Database schema specified
ORDER BY created_at ASC
LIMIT 100;
```

### Search path

:::info
[Read more](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) about the schema search path in the Postgres documentation.
:::

Another approach is to write direct SQL queries using unqualified table names (no database schema specified), then update the `search_path` setting to include the target database schema.

```sql [Set search path]
SET search_path TO my_ponder_project, "$user", public;
```

### Drizzle (`setDatabaseSchema`)

If you're writing direct SQL queries using Drizzle (e.g. from a downstream Node.js application), you can use the `setDatabaseSchema` utility function to target a specific database schema.

This function mutates a Drizzle schema such that any queries built using that schema will target the specified database schema. [Read more](https://orm.drizzle.team/docs/schemas) in the Drizzle documentation.

:::code-group

```ts [Drizzle]
import { setDatabaseSchema } from "@ponder/client"; // [!code focus]
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "../../ponder/ponder.schema"; // [!code focus]

setDatabaseSchema(schema, "target_schema"); // [!code focus]

const db = drizzle(process.env.DATABASE_URL, { schema, casing: "snake_case" });

const oldAccounts = await db
  .select()
  .from(schema.accounts)
  .orderBy(asc(schema.accounts.createdAt))
  .limit(100);

```

```sql [Generated SQL]
SELECT *
FROM target_schema.accounts -- Database schema specified
ORDER BY created_at ASC
LIMIT 100;
```

:::


